import time

from django.db import connection
from django.db.models import Q, F, Avg, Max, Count
from django.db.models.functions import Lower, Concat
from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt

from apis.models import School, Group, People, IdCard, ModelMeta
from djco.base.base_api import BaseApi
from djco.util.sql_util import SqlUtil

"""
    数据库测试
    QuerySet 文档  https://doc.bccnsoft.com/docs/django-docs-3.2-zh-hans/ref/models/querysets.html#explain
"""


@csrf_exempt
def sql_print(request):
    count = School.objects.count()
    print(count)
    print("connection.queries:", connection.queries)

    # 查询分析器
    sql = School.objects.filter(Q(status=1)).values()[0:5].explain()
    print("sql:", sql)
    return JsonResponse(BaseApi.api_ajax_success({}, "sql print"))


@csrf_exempt
def add(request, *args, **kwargs):
    """
    新增测试
    :param request:
    :param args:
    :param kwargs:
    :return:
    """
    print(request, args, kwargs)
    sc = School(level='Hi', name="名称" + str(time.time()), num1=time.time(), desc="这是i秒" + str(time.time()))
    sc.save()
    print(sc.get_level_display())  # 打印 choice value
    # 一对一新增
    card = IdCard(card_no='123456', card_desc=11)
    card.save()
    card1 = IdCard(card_no='654321', card_desc=22)
    card1.save()

    # 分别新增
    gp = Group.objects.create(name='分组2')  # 立马创建
    print("gp.d", gp.id)
    # peo = People.objects.create(name='成员1', age=10)
    # PeopleGroup.objects.create(people=peo, group=gp)  # 新增多对多关系

    # 多对多 批量新增
    peo2 = People.objects.create(id_card=card, name='成员3', age=10)
    peo3 = People.objects.create(id_card=card1, name='成员4', age=11)
    gp.peoples.set([peo2, peo3])  # 批量新增

    print(connection.queries)
    return JsonResponse(BaseApi.api_ajax_success({}, "新增"))


@csrf_exempt
def delete(request, *args, **kwargs):
    """
    删除测试
    :param request:
    :param args:
    :param kwargs:
    :return:
    """
    # 删除单个案例
    res = School.objects.filter(pk=request.POST['id']).delete()
    print(res, request, args, kwargs)

    # 多对多删除关系
    gp = Group.objects.get(id=24)
    gp.peoples.clear()  # 只删除对应的关系

    # 删除实体
    gp2 = Group.objects.get(id=25)
    gp2.delete()  # 删除实体+对应的关系

    return JsonResponse(BaseApi.api_ajax_success({}, "删除"))


@csrf_exempt
def edit(request, *args, **kwargs):
    """
    修改测试
    :param request:
    :param args:
    :param kwargs:
    :return:
    """
    param = request.POST
    sc = School(id=param['id'], name=param['name'], desc=param['desc'])
    sc.save()
    gp = Group(id=9, name="修改xxx")
    gp.save(update_fields=['name', 'updated'])  # 修改指定字段 updated 自动更新
    print(request, args, kwargs)
    return JsonResponse(BaseApi.api_ajax_success({}, "修改"))


@csrf_exempt
def index(request, *args, **kwargs):
    """
    列表测试
    :param request:
    :param args:
    :param kwargs:
    :return:
    """
    print(request, args, kwargs)
    # 单表查询
    data = School.objects.filter(Q(status=1)).values()[0:5]
    json_data = list(data)

    # 关联查询
    list1 = Group.objects.filter(peoples__age__gt=3).values('id', 'name', 'peoples__name')[100:105]
    print(list1)
    list2 = Group.objects.get(pk=1).peoples.values('id', 'name', 'age')
    print(list2)

    return JsonResponse(BaseApi.api_ajax_success(json_data, "获取列表"))


@csrf_exempt
def get(request, *args, **kwargs):
    """
    详情测试
    :param request:
    :param args:
    :param kwargs:
    :return:
    """
    data = School.objects.filter(pk=request.POST['id']).get()
    print(data, request, args, kwargs)

    # 级联查询
    pe = People.objects.filter(pk=1).values("id", "name", 'id_card_id',
                                            'id_card__card_no').first()  # first 查询不到不会报异常 且返回json
    print(pe)

    # 反向级联查询
    pe1 = IdCard.objects.filter(pk=3).values("id", "card_no", 'card_desc',
                                             'people__age').first()  # first 查询不到不会报异常 且返回json
    print(pe1)
    return JsonResponse(BaseApi.api_ajax_success(pe, "详情"))


@csrf_exempt
def model_meta(request):
    """
    Model 中 Meta 测试
    :param request:
    :return:
    """
    model = ModelMeta.objects.get(pk=2)
    print(model.pretty_date())  # 调用实例方法
    print(model)
    print(model.name_order_no)  # 调用实例属性
    return JsonResponse(BaseApi.api_ajax_success({}, "model_meta"))


@csrf_exempt
def complex_sql(request):
    """
    复杂查询
    :param request:
    :return:
    """
    # 多个filter 筛选 + 排序 + 分页+指定查询字段+ 跨表查询（获取的是QuerySet）
    list1 = People.objects.exclude(pk__lt=0).filter(name__contains='成').filter(age__gt=1)
    list1 = list1.order_by("-pk").values('id', 'age', 'id_card__card_no', names=Lower('name'),
                                         id_age=Concat('id', 'age'))[0:10]

    # 获取一个实体(People 对象)
    people = People.objects.exclude(pk__lt=0).first()
    # Q函数 F函数案例
    list2 = People.objects.filter(Q(pk__gte=F('id')), Q(name__contains='成'), Q(age__gt=65) | Q(age__lt=12))
    print(list1, people, list2)

    # 反向关联查询
    list3 = people.group_set.all()
    print(list3)

    # 聚合查询
    avg_age = People.objects.all().aggregate(age__avg=Avg('age'),
                                             age__max=Max('age'))  # 使用聚合函数 并且添加别名(别名需要__ 双下划线) 返回的是字典
    print(avg_age)  # 返回的是字典

    # 注解(分组)查询 （别名需要__ 双下划线）
    group_count1 = Count('group', filter=Q(group__id__gt=1))  # 条件注解分组
    list4 = People.objects.annotate(group__count=Count('group', distinct=True), group__count1=group_count1).order_by(
        'group__count')  # 如果语句中添加 filter， 则其顺序影响统计结果（筛选后分组 or 分组后筛选）
    print("list4--------------------")
    for p in list4:
        print(p.group__count, p.group__count1)

    # 原生语法
    with connection.cursor() as cursor:
        cursor.execute("UPDATE apis_people SET name = %s WHERE id = %s", ['成员修改', '1'])
        cursor.execute("UPDATE apis_people SET name = %s WHERE id = %s", ['成员修改2', '2'])
        cursor.execute(
            "SELECT a.id,a.name,b.card_no FROM apis_people  a left join apis_idcard  b on a.id_card_id=b.id WHERE a.id >1")
        row = SqlUtil.dictfetchall(cursor)  # 返回dict
        print(row)

        # 调用存储过程
        cursor.callproc('test_procedure', (3, 'aa'))  # 第一个参数是 IN  第二个参数是OUT
        cursor.execute('select @_test_procedure_1')  # select  @_存储过程名_参数顺序
        res = SqlUtil.dictfetchall(cursor)  # 返回存储过程结果
        print(res)

    #
    return JsonResponse(BaseApi.api_ajax_success(connection.queries, "complex_sql"))
